package dataBase;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

import dataBase.magaers.XsltDBManager;

	/**
	 * The class contains all the tables used in the MySql database
	 */
public class DBInitializer {
	
	private static DBInitializer instance = null;

	ConnectionManager connectionManager = null; 


/******************************************************************************
 *                     user related database tables                           *                  
 ******************************************************************************/
	List<ColumnValue> userPasswordList = null;
	ISqlTable userPasswordTable = null;
	
	List<ColumnValue> userInfoList = null;
	ISqlTable userInfoTable = null;
	
/******************************************************************************
 *                     code related database tables                           *                  
 ******************************************************************************/
	List<ColumnValue> codeCategoriesList = null;
	ISqlTable codeCategoriesTable = null;
	
	List<ColumnValue> codeList = null;
	ISqlTable codeTable = null;
	
	List<ColumnValue> codeTitleList = null;
	ISqlTable codeTitleTable = null;
	
/******************************************************************************
 *                     xsl related database tables                            *                  
 ******************************************************************************/
	List<ColumnValue> xslList = null;
	ISqlTable xslTable = null;
	
/**
 * @throws ClassNotFoundException ****************************************************************************/

	private DBInitializer() throws ClassNotFoundException{
		connectionManager = ConnectionManager.getInstance();
		
		// Singleton pattern
		userPasswordList = new ArrayList<ColumnValue>();
		userPasswordList.add(new ColumnValue("UID", "TEXT"));// email
		userPasswordList.add(new ColumnValue("Password", "TEXT"));
		
		userInfoList = new ArrayList<ColumnValue>();
		userInfoList.add(new ColumnValue("UID", "TEXT"));// email
		userInfoList.add(new ColumnValue("firstName", "TEXT"));
		userInfoList.add(new ColumnValue("lastName", "TEXT"));
		userInfoList.add(new ColumnValue("admin", "ENUM('true', 'false')"));

		codeCategoriesList = new ArrayList<ColumnValue>();
		codeCategoriesList.add(new ColumnValue("CID", "TEXT"));
		codeCategoriesList.add(new ColumnValue("category", "TEXT"));

		codeList = new ArrayList<ColumnValue>();
		codeList.add(new ColumnValue("CID", "TEXT"));
		codeList.add(new ColumnValue("Version", "TEXT"));
		codeList.add(new ColumnValue("UID", "TEXT"));
		codeList.add(new ColumnValue("Language", "TEXT"));
		codeList.add(new ColumnValue("Body", "LONGTEXT"));  
		codeList.add(new ColumnValue("Visibility", "TEXT"));

		codeTitleList = new ArrayList<ColumnValue>();
		codeTitleList.add(new ColumnValue("CID", "TEXT"));
		codeTitleList.add(new ColumnValue("Title", "TEXT"));
		
		xslList = new ArrayList<ColumnValue>();
		xslList.add(new ColumnValue("XID", "INTEGER"));
		xslList.add(new ColumnValue("XSL", "LONGTEXT"));
}
	public static DBInitializer getInstance() {
		if (instance == null){
			try {
				instance = new DBInitializer();
			} catch (ClassNotFoundException e) {
				return null;
			}
		}
		return instance;
	}
	
	public boolean initDB() throws ClassNotFoundException, SQLException{
		setUp();
		return true;
		
	}
	
	// TODO: select right engine
	/**
	 * Creates all the database tables in the MySql database
	 * @return true if all tables were created successfully otherwise false
	 * @throws SQLException 
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public boolean setUp() throws SQLException{

		// update concurrency settings		
		Statement stmt = connectionManager.getStatement(Connection.TRANSACTION_SERIALIZABLE);
		if (stmt == null){
			return false;
		}
		
		try{
			
			List<String> usersTablesPK = new LinkedList<String>();
			usersTablesPK.add("UID(128)");
			userPasswordTable = new UserPasswordSqlTable("UserPassword", userPasswordList, "innodb");
			userPasswordTable.createTable(stmt, usersTablesPK);
	
			userInfoTable = new SqlTable("UserInfo", userInfoList, "innodb");
			userInfoTable.createTable(stmt, usersTablesPK);
					
			List<String> codeCategoriesPK = new LinkedList<String>();
			List<String> codeCategoriesFT = new LinkedList<String>();
			codeCategoriesPK.add("cid(128)");
			codeCategoriesPK.add("category(128)");
			codeCategoriesFT.add("category");
			codeCategoriesTable = new CodeCategoriesTable("CodeCategories", codeCategoriesList, "MyISAM");
			codeCategoriesTable.createTable(stmt, codeCategoriesPK, codeCategoriesFT);// no Primary key for this table
	
			List<String> codesPK = new LinkedList<String>();
			codesPK.add("CID(128)");
			codesPK.add("Version(16)");
			codeTable = new CodeTable("Codes", codeList, "innodb");
			codeTable.createTable(stmt, codesPK);
	
			List<String> codeTitlePK = new LinkedList<String>();
			codeTitlePK.add("CID(128)");
			codeTitleTable = new CodeTitleTable("CodeTitles", codeTitleList, "innodb");
			codeTitleTable.createTable(stmt, codeTitlePK);
	
			List<String> xslPK = new LinkedList<String>();
			xslPK.add("XID");
			xslTable = new SqlTable("XslTable", xslList, "innodb");
			xslTable.createTable(stmt, xslPK);
			// init xslt first content
			XsltDBManager.getInstance().init();
			
			if (!connectionManager.commitAndClose(stmt)){
				return false;
			}
		}
		catch (SQLException exception){
			connectionManager.rollbackAndClose(stmt);
			throw exception;
		}
		return true;
	}
	public ISqlTable getUserPasswordTable() {
		return userPasswordTable;
	}
	public ISqlTable getUserInfoTable() {
		return userInfoTable;
	}
	public ISqlTable getCodeCategoriesTable() {
		return codeCategoriesTable;
	}
	public ISqlTable getCodeTable() {
		return codeTable;
	}
	public ISqlTable getCodeTitleTable() {
		return codeTitleTable;
	}
	public ISqlTable getXslTable() {
		return xslTable;
	}


	

	
}
